﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net.Mail;
using System.Text;
using CRMFramework;
using Newtonsoft.Json;
using System.Web;
using System.IO;
using System.Net;
using System.Configuration;

namespace CRMBiz
{
    public static class ClientProfileHandler
    {
        /// <summary>
        /// function for get selected data
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindDataTableForApplication(Int64 appid, string OperatorID)
        {
            string sql = string.Empty;
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                    DBhelper.SetParameter("@OperatorID", OperatorID),
                    DBhelper.SetParameter("@LatestTime", DateTime.Now),
                };
                sql = " SELECT crm_application.*,udf_getformatedDate(crm_application.CreatedDate) AS cdate,crm_contact.`ContactID`,`crm_contact`.`FirstName`,`crm_contact`.`SurName`,`crm_contact`.`MiddleName`,crm_contact.Mobile,crm_contact.Email" +
                    " FROM crm_application LEFT JOIN `crm_applicationcontact` ON `crm_applicationcontact`.`app_id`=`crm_application`.`app_id` " +
                    " LEFT JOIN  `crm_contact` ON  crm_contact.`ContactID`=`crm_applicationcontact`.`ContactID` WHERE `crm_application`.`app_id`=" + appid;
                //sql = "SELECT crm_contact.FirstName,crm_contact.MiddleName,crm_contact.SurName,crm_contact.Email,(SELECT  DesiredAmount FROM  crm_application  WHERE (app_id = '" + appid + "')) AS DesiredAmount,(SELECT  OfferedAmount FROM  crm_application  WHERE (app_id = '" + appid + "')) AS OfferedAmount,crm_application.LoadNo,crm_application.Unsubscribe_Ad,crm_application.Memo,crm_application.OutSrcLenderVal,crm_application.ReferrerVal,crm_application.app_result,crm_application.TextAwaitDoc,(SELECT  PurposeSummary FROM  crm_application  WHERE (app_id = '" + appid + "')) AS PurposeSummary,crm_application.amountMoto as Motor,crm_application.amoutProperties as Property,CASE WHEN CreatedDate=CURDATE() THEN CONCAT('Today',' ',DATE_FORMAT(CreatedDate,'%h:%i %p'))WHEN CreatedDate=subdate(current_date, 1) THEN CONCAT('Yesterday',' ',DATE_FORMAT(CreatedDate,'%h:%i %p'))WHEN CreatedDate!=CURDATE() THEN DATE_FORMAT(CreatedDate,'%d-%b-%Y %h:%i %p')END as CreatedDate,(SELECT  deletedFlag FROM  crm_application  WHERE (app_id ='" + appid + "' )) AS deletedFlag FROM  crm_application RIGHT OUTER JOIN crm_applicationcontact LEFT OUTER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID ON crm_application.app_id = crm_applicationcontact.app_id WHERE (crm_applicationcontact.NoInApplication = 1) AND (crm_applicationcontact.app_id ='" + appid + "')";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
                DBhelper.ExecuteNonQuery("insert into crm_Recent(app_id,OperatorID,LatestTime) values(@app_id,@OperatorID,@LatestTime)", para);
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function for get folder:
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable GetFolderName(Int64 appid)
        {
            string sql = string.Empty;
            DataTable dt = null;
            try
            {
                sql = "select Folder from crm_application where app_id='" + appid + "' and deletedFlag=0";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }


        public static DataTable BindDataTableForApplicationEmailTemplete(Int64 appid)
        {
            DataTable dt = null;
            StringBuilder sqlqry = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", appid),
                };

                sqlqry = new StringBuilder("select crm_application.*, crm_contact.*,am_loan1.* from crm_application , crm_contact,am_loan1 where crm_application.app_id=@app_id and crm_contact.app_id=@app_id and am_loan1.app_id=@app_id;");
                dt = DBhelper.ExecuteDataset(sqlqry.ToString(), para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function for update Application Profile data and Save History
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool UpdateAppDetailData(string appid, string desiredAmount, string offeredAmount, string resultDescription, string loadNo, string folder, string motorCount, string propertyCount, string hdnTextAwaitDoc, string purposeSummary, string memo, string outSrcLenderVal, string referrerVal, string oprator, int iscarowner)
        {
            bool flag = false;
            string sql = string.Empty;

            try
            {
                var param = new[] {
                    DBhelper.SetParameter("@p_appID", appid),
                    DBhelper.SetParameter("@p_app_result", resultDescription),
                    DBhelper.SetParameter("@p_LoadNo", loadNo),
                    DBhelper.SetParameter("@p_NoInApplication", 1),
                    DBhelper.SetParameterForApp("@p_Folder",folder),
                    DBhelper.SetParameter("@p_OfferedAmount",offeredAmount),
                    DBhelper.SetParameterForApp("@p_DesiredAmount",desiredAmount),
                    DBhelper.SetParameter("@p_amountMoto",motorCount),
                    DBhelper.SetParameterForApp("@p_amoutProperties",propertyCount),
                    DBhelper.SetParameterForApp("@p_TextAwaitDoc",hdnTextAwaitDoc),
                    DBhelper.SetParameterForApp("@p_purposeSummary",purposeSummary),
                    //DBhelper.SetParameterForApp("@p_Unsubscribe_Ad",unsubscribe_Ad),
                    DBhelper.SetParameterForApp("@p_Memo",memo),
                    DBhelper.SetParameterForApp("@p_OutSrcLenderVal",outSrcLenderVal),
                    DBhelper.SetParameterForApp("@p_ReferrerVal",referrerVal),
                    DBhelper.SetParameterForApp("@p_Operator",oprator),
                    DBhelper.SetParameterForApp("@p_IsCarOwner",iscarowner),
                };
                DBhelper.ExecuteNonQuerySP("sp_UpdateApplicationProfile", param);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }


        /// <summary>
        /// function for update history table
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool UpdateHistoryData(int app_id, string HistoryDate, string HistoryTime, string Action, string Operator, string Description)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@HistoryDate", HistoryDate),
                    DBhelper.SetParameter("@HistoryTime", HistoryTime),
                    DBhelper.SetParameter("@Action", Action),
                    DBhelper.SetParameter("@Operator", Operator),
                    DBhelper.SetParameter("@Description", Description),
                };

                string sbSql = "insert into crm_history(app_id, HistoryDate, HistoryTime, Action, Operator, Description) values(@app_id, @HistoryDate, @HistoryTime, @Action, @Operator, @Description)";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        /// <summary>
        /// function for search applicants for combine app
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable SearchCombineApplicants(Int64 appid)
        {
            string sql = string.Empty;
            DataTable dt = null;
            try
            {
                sql = "SELECT crm_contact.FirstName, crm_contact.SurName,crm_contact.ContactID,crm_applicationcontact.combineApp FROM crm_applicationcontact INNER JOIN crm_application ON crm_applicationcontact.app_id = crm_application.app_id INNER JOIN crm_contact ON crm_applicationcontact.ContactID = crm_contact.ContactID where crm_applicationcontact.app_id ='" + appid + "' and (crm_applicationcontact.deletedFlag=0 or combineApp!='') and crm_application.deletedFlag=0 LIMIT 3";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to bind last result
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindLastResultForClientProfile()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT * FROM crm_lastresult ORDER BY OrderNo ";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to bind awaited docs
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string BindAwaitedDocsForClientProfile()
        {
            string str = "";
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();

            sql = new StringBuilder("SELECT AwaitedDocs  FROM crm_awaitdocs");
            dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                ArrayList oList = new ArrayList();
                string strJson = JsonConvert.SerializeObject(dt);
                str += "[";
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str += "{";
                    for (int j = 0; j <= dt.Columns.Count - 1; j++)
                    {
                        oList.Add(dt.Columns[j].ColumnName + ":" + dt.Rows[i][j].ToString());
                        if (dt.Columns.Count == j + 1)
                        {
                            str += Convert.ToString("\"" + dt.Columns[j].ColumnName + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                        }
                        else
                        {
                            str += Convert.ToString("\"" + dt.Columns[j].ColumnName + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"" + ",");
                        }
                    }
                    if (dt.Rows.Count == i + 1)
                    {
                        str += "}";
                    }
                    else
                    {
                        str += "},";
                    }
                }
                str += "]";
            }
            return str;
        }

        /// <summary>
        /// function to insert combine app data
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string InsertCombineApplicantData(int app_id, int contactID, int c_app_id, string combineAppId, int noInApplication)
        {
            string sbSql_af = "";
            string sb_Af = "";
            string sb_app = "";
            string sb_appCon = "";
            string sbAf = "";
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@ContactID", contactID),
                };

                StringBuilder sql = new StringBuilder();
                DataTable dt = new DataTable();

                //Check the count in applicationcontact table:
                sql = new StringBuilder("SELECT COUNT( * ) AS total FROM crm_applicationcontact WHERE app_id ='" + app_id + "' AND ContactID ='" + contactID + "' AND NoInApplication='" + noInApplication + "'  AND CombineApp ='" + c_app_id + "'");
                dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
                if (Convert.ToInt32(dt.Rows[0]["total"]) != 0)
                {

                    return ("");
                }

               //Condition 1: if number of application<>0:
                else if (noInApplication != 0)
                {
                    DataTable dts = new DataTable();
                    string finalQuery = string.Empty;
                    string selSql = "select app_info.app_id FROM app_info INNER JOIN crm_applicationappform ON  crm_applicationappform.AppFormId = app_info.app_id WHERE crm_applicationappform.ApplicationId='" + c_app_id + "' limit 1";
                    dts = DBhelper.ExecuteDataset(selSql.ToString()).Tables[0];
                    //Insert data into crm_applicationcontact table & crm_appcontactchecklist table & crm_applicationappform:
                    string sbSql_ac = "insert into crm_applicationcontact values(0,'" + app_id + "','" + contactID + "','" + noInApplication + "','" + c_app_id + "',0)" + ";";
                    string sbSql_app = "update crm_application set deletedFlag=1 where app_id='" + c_app_id + "'" + ";";
                    if (dts.Rows.Count != 0)
                    {
                        sbSql_af = "insert into crm_applicationappform values(0,'" + app_id + "','" + dts.Rows[0]["app_id"] + "')" + ";";
                    }

                    string cnt = DBhelper.ExecuteScalar("Select count(*) from crm_appcontactchecklist where app_id=" + app_id);

                    if (int.Parse(cnt) > 0)
                    {
                        string sbSql_vc = "insert into crm_appcontactchecklist(ApplicationContactID,app_id) values((SELECT LAST_INSERT_ID(ApplicationContactID) FROM `crm_applicationcontact` WHERE app_id='" + app_id + "' order by ApplicationContactID desc limit 1),'" + app_id + "')";
                        finalQuery = sbSql_ac + sbSql_app + sbSql_af + sbSql_vc;
                    }
                    else
                        finalQuery = sbSql_ac + sbSql_app + sbSql_af;

                    DBhelper.ExecuteNonQuery(finalQuery, para);
                    return ("1");
                }

                //Condition 2: if number of application==0:
                else if (noInApplication == 0)
                {
                    DataTable dts = new DataTable();
                    string selSql = "select app_info.app_id FROM app_info INNER JOIN crm_applicationappform ON  crm_applicationappform.AppFormId = app_info.app_id WHERE crm_applicationappform.ApplicationId='" + c_app_id + "' limit 1";
                    dts = DBhelper.ExecuteDataset(selSql.ToString()).Tables[0];


                    if (app_id != c_app_id)
                    {
                        //delete data from crm_applicationcontact table & crm_appcontactchecklist table & crm_applicationappform:
                        string sbVc = "DELETE FROM crm_appcontactchecklist WHERE ApplicationcontactID IN (SELECT ApplicationcontactID FROM crm_applicationcontact where app_id='" + app_id + "' and ContactID='" + contactID + "' and CombineApp='" + c_app_id + "' )" + ";";
                        string sbSql = "delete from crm_applicationcontact where app_id='" + app_id + "' and ContactID='" + contactID + "' and  CombineApp='" + c_app_id + "'" + ";";
                        if (dts.Rows.Count != 0)
                        {
                            sbAf = "delete from crm_applicationappform where ApplicationId='" + app_id + "' and AppFormId='" + dts.Rows[0]["app_id"] + "' ";
                        }
                        string finalQuery = sbVc + sbSql + sbAf;
                        DBhelper.ExecuteNonQuery(finalQuery, para);
                        return ("2");
                    }
                    //Condition 3: if app_id=combineAppId:
                    if (app_id == c_app_id)
                    {
                        string sqlstring_getAppformId = " SELECT AppFormId FROM morganse_crm.crm_applicationappform where applicationid='" + +c_app_id + "' order by id desc limit 1";
                        dts = DBhelper.ExecuteDataset(sqlstring_getAppformId).Tables[0];

                        if (combineAppId != "")
                        {
                            sb_app = "update crm_application set deletedFlag=0 where app_id='" + combineAppId + "'" + ";";
                            sb_appCon = "delete from crm_applicationcontact where app_id='" + app_id + "' and ContactID='" + contactID + "' and  CombineApp='" + combineAppId + "'" + ";";
                            sb_Af = "delete from crm_applicationappform where ApplicationId='" + app_id + "' and AppFormId='" + dts.Rows[0]["AppFormId"] + "' ";
                            string sbVc = "DELETE FROM crm_appcontactchecklist WHERE ApplicationcontactID IN (SELECT ApplicationcontactID FROM crm_applicationcontact where app_id='" + app_id + "' and ContactID='" + contactID + "' and CombineApp='" + combineAppId + "' )" + ";";
                            string finalQry = sbVc + sb_app + sb_appCon + sb_Af;
                            DBhelper.ExecuteNonQuery(finalQry, para);
                            return ("2");
                        }
                        else
                        {
                            //if app_id== current app_id then delete from crm_applicationcontact table:
                            string sbSql = "update crm_application set deletedFlag=1 where app_id='" + app_id + "'" + ";";
                            DBhelper.ExecuteNonQuery(sbSql, para);
                            return ("2");
                        }

                    }
                }
            }
            catch (Exception)
            {
                throw;
            }
            return ("success");
        }


        /// <summary>
        /// function to bind Email Template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string BindEmailTemplate()
        {
            string str = "";
            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();

            sql = new StringBuilder("SELECT EmailTemplate,TemplateFormat  FROM crm_emailtemplate");
            dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
            if (dt.Rows.Count > 0)
            {
                ArrayList oList = new ArrayList();
                string strJson = JsonConvert.SerializeObject(dt);
                str += "[";
                for (int i = 0; i <= dt.Rows.Count - 1; i++)
                {
                    str += "{";
                    for (int j = 0; j <= dt.Columns.Count - 1; j++)
                    {
                        oList.Add(dt.Columns[j].ColumnName + ":" + dt.Rows[i][j].ToString());
                        if (dt.Columns.Count == j + 1)
                        {
                            str += Convert.ToString("\"" + dt.Columns[j].ColumnName + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"");
                        }
                        else
                        {
                            str += Convert.ToString("\"" + dt.Columns[j].ColumnName + "\"" + ":" + "\"" + dt.Rows[i][j].ToString() + "\"" + ",");
                        }
                    }
                    if (dt.Rows.Count == i + 1)
                    {
                        str += "}";
                    }
                    else
                    {
                        str += "},";
                    }
                }
                str += "]";
            }
            return str;
        }

        /// <summary>
        /// function to update email template
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool UpdateEmailTemplate(string emailTemplate, string templateFormat)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@emailTemplate", emailTemplate),
                    DBhelper.SetParameter("@TemplateFormat", templateFormat),
                };

                string sbSql = "update crm_emailtemplate SET TemplateFormat ='" + templateFormat + "' WHERE EmailTemplate='" + emailTemplate + "'";
                DBhelper.ExecuteNonQuery(sbSql, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        private static string getOnlyFileName(string fullPath)
        {
            fullPath = fullPath.Replace('\\', ';');
            string[] arr = fullPath.Split(';');
            string filename = arr[arr.Length - 1];
            return filename;

        }

        /// <summary>
        /// function to send email
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string SendEmail(string fromName, string password, string toName, string attachmentFile, string attachmentsDrag, string subject, string bodyContent)
        {
            MailMessage mail = new MailMessage();
            mail.To.Add(toName);
            var obj = fromName;
            mail.From = new MailAddress(obj.ToString(), "Morgan Finance");
            mail.Subject = subject;
            mail.Body = bodyContent;
            mail.IsBodyHtml = true;
            string attachmentList = string.Empty;

            if (attachmentFile != "")
            {
                string folderPath = System.Web.HttpContext.Current.Server.MapPath("/uploads");
                attachmentFile = folderPath + "\\" + attachmentFile;
                string dirName = Path.GetDirectoryName(attachmentFile);
                if (attachmentFile != "")
                {
                    string[] fileEntries = Directory.GetFiles(dirName);
                    foreach (string fileName in fileEntries)
                    {
                        mail.Attachments.Add(new Attachment(fileName));
                        attachmentList += getOnlyFileName(fileName) + " ,";
                    }
                }
            }
            else if (attachmentFile == "")
            {
                attachmentFile = "";
            }
            if (attachmentsDrag != "")
            {
                attachmentsDrag = HttpContext.Current.Server.MapPath(attachmentsDrag);
                string dirNameDg = Path.GetDirectoryName(attachmentsDrag);
                if (attachmentsDrag != "")
                {
                    string[] findfiles = Directory.GetFiles(dirNameDg);
                    foreach (string dragFileName in findfiles)
                    {
                        mail.Attachments.Add(new Attachment(dragFileName));
                        attachmentList += getOnlyFileName(dragFileName) + " ,";

                    }
                }
            }
            else
            {
                attachmentsDrag = "";
            }

            SmtpClient smtp = new SmtpClient();
            smtp.Host = "smtp.gmail.com";
            smtp.Port = 587;
            smtp.EnableSsl = true;
            smtp.UseDefaultCredentials = true;
            smtp.Timeout = 2000000;
            smtp.Credentials = new System.Net.NetworkCredential(fromName, password);
            smtp.EnableSsl = true;
            smtp.Send(mail);
            mail.Dispose();

            if (attachmentFile != "")
            {
                string dirNamea = Path.GetDirectoryName(attachmentFile);
                string[] fileEntriess = Directory.GetFiles(dirNamea);
                foreach (string doc in fileEntriess)
                {
                    File.Delete(doc);
                }
            }
            else
            { }
            if (attachmentsDrag != "")
            {
                string dirNamea = Path.GetDirectoryName(attachmentsDrag);
                string[] fileEntriess = Directory.GetFiles(dirNamea);
                foreach (string doc in fileEntriess)
                {
                    File.Delete(doc);
                }
            }
            else
            { }
            return attachmentList;
        }



        /// <summary>
        /// function to get all contact list
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable GetAllContactOfApplicant(int app_id, int contact_id)
        {
            DataTable dt = null;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@app_id", app_id),
                    DBhelper.SetParameter("@ContactID", contact_id),
                };
                string sql = string.Empty;
                if (app_id < 0 && contact_id > 0)
                    sql = "SELECT crm_contact.FirstName, crm_contact.MiddleName, crm_contact.SurName, crm_contact.Title, crm_contact.Mobile, crm_contact.Email, crm_contact.HomePhone, crm_contact.State,CAST(DATE_FORMAT(crm_contact.DateBirth,'%d %b %Y') as Char)  AS DateBirth, udf_getformatedDate(CreateDate) AS CreateDate, crm_contact.ContactID FROM crm_contact WHERE crm_contact.ContactID=@ContactID ORDER By CreateDate Desc";
                else if (app_id > 0)
                    sql = "SELECT crm_contact.FirstName, crm_contact.MiddleName, crm_contact.SurName, crm_contact.Title, crm_contact.Mobile, crm_contact.Email, crm_contact.HomePhone, crm_contact.State, CAST(DATE_FORMAT(crm_contact.DateBirth,'%d %b %Y') as Char)  AS DateBirth,udf_getformatedDate(CreateDate) AS CreateDate, crm_contact.ContactID FROM crm_contact INNER JOIN crm_applicationcontact ON crm_contact.ContactID = crm_applicationcontact.ContactID INNER JOIN crm_application ON crm_applicationcontact.app_id=crm_application.app_id WHERE crm_application.app_id=@app_id and crm_applicationcontact.deletedFlag=0 and crm_application.deletedFlag=0 ORDER By crm_applicationcontact.NoInApplication";
                dt = DBhelper.ExecuteDataset(sql, para).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function for advanced application search
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataSet GetAdvancedSearch(int pageIndex, int pageSize, string searchtext)
        {
            DataSet ds;
            try
            {
                if (!string.IsNullOrEmpty(searchtext))
                {
                    String[] Words = searchtext.Split(' ');
                    if (Words.Length == 2)
                    {
                        var sbWhere = new StringBuilder(" WHERE ")
                            .AppendFormat("FirstName like '%{0}%'", Words[0])
                            .AppendFormat(" AND SurName like '%{0}%'", Words[1])
                            .AppendFormat(" OR Mobile like '%{0}%'", searchtext)
                            .AppendFormat(" OR Email like '%{0}%'", searchtext);
                        var sbSql = new StringBuilder("SELECT *,(select count(*) from crm_contact");
                        sbSql.Append(sbWhere);
                        sbSql.Append(") as Total from crm_contact");
                        sbSql.Append(sbWhere);
                        sbSql.Append(" order by app_id");
                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                    else
                    {
                        var sbWhere = new StringBuilder(" WHERE ")
                            .AppendFormat("FirstName like '%{0}%'", searchtext)
                            .AppendFormat(" OR SurName like '%{0}%'", searchtext)
                            .AppendFormat(" OR Mobile like '%{0}%'", searchtext)
                            .AppendFormat(" OR Email like '%{0}%'", searchtext);
                        var sbSql = new StringBuilder("SELECT *,(select count(*) from crm_contact");
                        sbSql.Append(sbWhere);
                        sbSql.Append(") as Total from crm_contact");
                        sbSql.Append(sbWhere);
                        sbSql.Append(" order by app_id");
                        sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                        ds = DBhelper.ExecuteDataset(sbSql.ToString());
                    }
                }
                else
                {
                    var sbSql = new StringBuilder("SELECT *,(select count(*) from crm_contact) as Total from crm_contact order by app_id");
                    sbSql.Append(" LIMIT ").Append((pageIndex - 1) * pageSize).Append(", ").Append(pageSize);
                    ds = DBhelper.ExecuteDataset(sbSql.ToString());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }


        /// <summary>
        /// function for update  contacthistory table when send email
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool UpdateContactHistoryForEmail(int ContactID, string HistoryDate, string HistoryTime, string Action, string Operator, string Description)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ContactID", ContactID),
                    DBhelper.SetParameter("@HistoryDate", HistoryDate),
                    DBhelper.SetParameter("@HistoryTime", HistoryTime),
                    DBhelper.SetParameter("@Action", Action),
                    DBhelper.SetParameter("@Operator", Operator),
                    DBhelper.SetParameter("@Description", Description),
                };

                string sbSql = "insert into crm_contacthistory(ContactID, HistoryDate,HistoryTime, Action, Operator, Description) values(@ContactID, @HistoryDate,@HistoryTime, @Action, @Operator, @Description)" + ";";
                string sbEdidate = "update crm_contact set EditDate='" + HistoryDate + "' where ContactID='" + ContactID + "'";
                string val = sbSql + sbEdidate;
                DBhelper.ExecuteNonQuery(val, para);
                flag = true;
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }


        /// <summary>
        /// function to add new application-profile
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>


        public static DataSet AddNewApplicationProfile(int ContactID, int NoInApplication, string CombineApp, string DesiredAmount, string OfferedAmount, string app_result, string LoadNo, int NoInApplications, string Folder, string amountMoto, string amoutProperties, string TextAwaitDoc, string purposeSummary, string memo, string outSrcLenderVal, string referrerVal, string Operator, int IscarOwner)
        {
            DataSet ds;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@p_ContactID",ContactID),
                    DBhelper.SetParameter("@p_NoInApplication",NoInApplication),
                    DBhelper.SetParameter("@p_CombineApp",CombineApp),
                    DBhelper.SetParameter("@p_app_LoanAmount",DesiredAmount),
                    DBhelper.SetParameter("@p_OfferedAmount",OfferedAmount),
                    DBhelper.SetParameter("@p_app_result",app_result),
                    DBhelper.SetParameter("@p_LoadNo",LoadNo),
                    
                    DBhelper.SetParameter("@p_NoInApplications",NoInApplications),
                    DBhelper.SetParameter("@p_Folder",Folder),
                    DBhelper.SetParameter("@p_amountMoto",amountMoto),
                    DBhelper.SetParameter("@p_amoutProperties",amoutProperties),
                    DBhelper.SetParameter("@p_TextAwaitDoc",TextAwaitDoc),
                    DBhelper.SetParameter("@p_purposeSummary",purposeSummary),
                  //  DBhelper.SetParameterForApp("@p_Unsubscribe_Ad",unsubscribe_Ad),
                    DBhelper.SetParameterForApp("@p_Memo",memo),
                    DBhelper.SetParameterForApp("@p_OutSrcLenderVal",outSrcLenderVal),
                    DBhelper.SetParameterForApp("@p_ReferrerVal",referrerVal),
                    DBhelper.SetParameter("@p_Operator",Operator),
                    DBhelper.SetParameter("@p_IsCarOwner",IscarOwner),
                };

                ds = DBhelper.ExecuteDatasetSP("sp_addNewApplicationProfile", para);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return ds;
        }

        /// <summary>
        /// function to delete application(not usefull)
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool DeleteApplication(string app_id)
        {
            bool flag = false;
            try
            {
                var para = new[] 
                {
                    DBhelper.SetParameter("@app_id", app_id),
                };

                string sbApplication = "delete from crm_application where app_id=@app_id;";
                string sbAppCon = "delete from crm_applicationcontact where app_id=@app_id;";
                string sbLoanInfo = "delete from crm_loaninfo where app_id=@app_id;";
                string sbAppExtnd = "delete from crm_applicationextend where app_id=@app_id;";
                string finalQuery = sbApplication + sbAppCon + sbLoanInfo + sbAppExtnd;

                DBhelper.ExecuteNonQuery(finalQuery, para);
                flag = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return flag;
        }

        /// <summary>
        /// function to bind app-forms list
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindAppForms(int pageindex, int pagesize, int app_id)
        {
            string sql = string.Empty;
            DataTable dt = null;
            try
            {
                sql = "select app_info.app_id,udf_getformatedDate(CONCAT(app_info.app_Date,' ',app_info.app_time)) as app_Date,app_info.app_Date as 'app_Date1',Concat('$',Format(app_info.app_LoanAmount,',')) as DesiredAmount,app_info.app_ApplyWithSomeoneWhoOwns as assets,CONCAT( IFNULL( app_FirstName1,  '' ) ,  ' ', IFNULL( app_Middlename1,  '' ) ,  ' ', IFNULL( app_Surname1,  '' )) AS FullName FROM app_info INNER JOIN crm_applicationappform ON app_info.app_id = crm_applicationappform.AppFormId WHERE crm_applicationappform.ApplicationId='" + app_id + "'";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

        /// <summary>
        /// function to remove duplicate
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static bool RemoveDuplicateData(int applicationId, int appformId)
        {
            bool flag = false;
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ApplicationId", applicationId),
                    DBhelper.SetParameter("@AppFormId", appformId), 
                };

                StringBuilder sql = new StringBuilder();
                DataTable dt = new DataTable();
                sql = new StringBuilder("select COUNT( * ) AS total from crm_application where app_id='" + appformId + "'");
                dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
                if (Convert.ToInt32(dt.Rows[0]["total"]) != 0)
                {
                    DataTable dts = new DataTable();
                    string selSql = "select app_info.app_id FROM app_info INNER JOIN crm_applicationappform ON  crm_applicationappform.AppFormId = app_info.app_id WHERE crm_applicationappform.ApplicationId='" + applicationId + "' limit 1";
                    dts = DBhelper.ExecuteDataset(selSql.ToString()).Tables[0];
                    string sbSql_ac = "insert into crm_applicationappform values(0,'" + appformId + "','" + dts.Rows[0]["app_id"] + "')";
                    DBhelper.ExecuteNonQuery(sbSql_ac, para);
                    flag = true;
                    return flag;
                }
                else if (Convert.ToInt32(dt.Rows[0]["total"]) == 0)
                {
                    Exception ex = new Exception();
                    throw ex;
                }
            }
            catch (Exception)
            {
                throw;
            }
            return flag;
        }

        /// <summary>
        /// function to delete extra forms
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static string DeleteOldForms(string app_id)
        {
            try
            {
                var para = new[] {
                    DBhelper.SetParameter("@ApplicationId", app_id),
                    
                };

                StringBuilder sql = new StringBuilder();
                DataTable dt = new DataTable();
                sql = new StringBuilder("SELECT (SELECT COUNT( * ) FROM am_loan1 WHERE app_id =  '" + app_id + "') AS LoanCount, (SELECT IFNULL(SUM(Income_Payslip + Bank_LatestMonth+Bank_FullName+Bank_ACBSB+Assert_CouncilRate+Assert_Vehicles+Assert_CouncilRate+Assert_Mortage+Assert_Valuation+IDs_Residential_Rental+Residential_Rate+IDs_Residential_Boarding+IDs_UtilityBill+IDs_PhotoID+IDs_Card+ExtraItemNo1+ExtraItemNo2+ExtraItemNo3+ExtraItemNo4+ExtraItemNo5+ExtraItemNo6+ExtraItemNo7+ExtraItemNo8+ExtraItemNo9+ExtraItemNo10 ),0) AS CheckCount FROM  `crm_appcontactchecklist` WHERE app_id =  '" + app_id + "') AS CheckCount");
                dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];

                if ((Convert.ToInt32(dt.Rows[0]["LoanCount"]) == 0) & (Convert.ToInt32(dt.Rows[0]["CheckCount"]) == 0))
                {
                    string sbSql_ac = "delete FROM crm_applicationappform WHERE ApplicationId='" + app_id + "' and AppFormId='" + app_id + "'" + ";";
                    string sbSql_app = "update crm_application set deletedFlag=1 where app_id='" + app_id + "'";
                    string SqlQuery = sbSql_ac + sbSql_app;
                    DBhelper.ExecuteNonQuery(SqlQuery, para);
                    return ("Application Deleted Successfully.");
                }
                else if ((Convert.ToInt32(dt.Rows[0]["CheckCount"]) != 0) && (Convert.ToInt32(dt.Rows[0]["LoanCount"]) != 0))
                {
                    return ("Loan and check list is exist you can not delete this application.");
                }
                else if (Convert.ToInt32(dt.Rows[0]["CheckCount"]) != 0)
                {
                    return ("Check list is exist you can not delete this application.");

                }
                else if (Convert.ToInt32(dt.Rows[0]["LoanCount"]) != 0)
                {
                    return ("Loan is exist you can not delete this application.");
                }
            }
            catch (Exception)
            {
                throw;
            }
            return ("success");
        }

        /// <summary>
        /// function to bind outsourceLender
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindOutSourceLender()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT * FROM crm_outsourcelender";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to bind Referrer
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public static DataTable BindReferrerData()
        {
            DataTable dt;
            try
            {
                string sql = "SELECT * FROM crm_referrer";
                dt = DBhelper.ExecuteDataset(sql).Tables[0];
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

        /// <summary>
        /// function to show applicants first and last name
        /// </summary>
        /// <param name="key"></param>
        /// <param name="value"></param>
        /// <returns></returns>

        public static DataTable GetApplicantNames(int app_id)
        {

            StringBuilder sql = new StringBuilder();
            DataTable dt = new DataTable();
            try
            {
                sql = new StringBuilder("SELECT CONCAT(CONCAT(UCASE(LEFT(crm_contact.FirstName,1)),SUBSTRING(crm_contact.FirstName,2)),' ', CONCAT(UCASE(LEFT(crm_contact.MiddleName,1)),SUBSTRING(crm_contact.MiddleName,2)),' ',UCASE(crm_contact.SurName)) as FullName,crm_contact.ContactID FROM crm_contact INNER JOIN crm_applicationcontact ON crm_contact.ContactID = crm_applicationcontact.ContactID WHERE crm_applicationcontact.app_id ='" + app_id + "' LIMIT 3");
                dt = DBhelper.ExecuteDataset(sql.ToString()).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }

    }
}
